﻿Imports System.Data.SqlServerCe
Imports Framework.Data

Public Class SharedItemsFilesEx
    Shared Sub New()
        Dim aSelectColumns As String() = {"Id", "FileName", "Directory", "SHA512Hash" _
                                        , "Size", "SharedItemId", "Added", "LastChanged" _
                                        , "OrphanedSince", "SHA512HashForFirstMb", "SHA512HashForLastMb"}
        ObjectPersister.DefineObject(GetType(Entities.SharedItemFile), "SharedItemsFiles", aSelectColumns)
    End Sub
    Private Sub New()
    End Sub

    Public Shared Function GetAllByDirectory(ByVal directory As String) As Generic.List(Of Entities.SharedItemFile)
        Return ObjectPersister.GetAll(Of Entities.SharedItemFile)(String.Format("Directory='{0}' AND OrphanedSince IS NULL", directory))
    End Function

    Public Shared Function GetAllBySharedItemId(ByVal sharedItemId As Integer) As Generic.List(Of Entities.SharedItemFile)
        Return ObjectPersister.GetAll(Of Entities.SharedItemFile)(String.Format("SharedItemId='{0}'", sharedItemId))
    End Function
    Public Shared Function GetAllBySharedItemId(ByVal sharedItemId As Integer, ByVal transactionScope As TransactionScope) As Generic.List(Of Entities.SharedItemFile)
        Return ObjectPersister.GetAll(Of Entities.SharedItemFile)(String.Format("SharedItemId='{0}'", sharedItemId))
    End Function

    Public Shared Function GetAllByFileNameAndSize(ByVal filename As String, ByVal size As Long) As Generic.List(Of Entities.SharedItemFile)
        Dim strClause As String = "[FileName]=@Filename AND [Size]=@Size"
        Dim sqlPar1 As New SqlCeParameter("@Filename", filename)
        Dim sqlPar2 As New SqlCeParameter("@Size", size)
        Return ObjectPersister.GetAll(Of Entities.SharedItemFile)(strClause, sqlPar1, sqlPar2)
    End Function
    Public Shared Function GetAllBySize(ByVal size As Long) As Generic.List(Of Entities.SharedItemFile)
        Dim strClause As String = "[Size]=@Size"
        Dim sqlPar1 As New SqlCeParameter("@Size", size)
        Return ObjectPersister.GetAll(Of Entities.SharedItemFile)(strClause, sqlPar1)
    End Function

    Public Shared Function GetAllBySHA512Hash(ByVal sha512Hash As String) As Generic.List(Of Entities.SharedItemFile)
        Dim strClause As String = String.Format("[Sha512Hash]=@Hash", sha512Hash)
        Dim sqlPar1 As New SqlCeParameter("@Hash", sha512Hash)
        Return ObjectPersister.GetAll(Of Entities.SharedItemFile)(strClause, sqlPar1)
    End Function
    Public Shared Function GetBySHA512HashAndSizeNotOrphaned(ByVal sha512Hash As String, ByVal size As Long) As Entities.SharedItemFile
        Dim strClause As String = "[Sha512Hash]=@Hash AND [Size]=@Size AND OrphanedSince IS NULL"
        Dim sqlPar1 As New SqlCeParameter("@Hash", sha512Hash)
        Dim sqlPar2 As New SqlCeParameter("@Size", size)
        Return ObjectPersister.GetOne(Of Entities.SharedItemFile)(strClause, sqlPar1, sqlPar2)
    End Function
    Public Shared Function GetBySHA512HashAndSize(ByVal sha512Hash As String, ByVal size As Long) As Entities.SharedItemFile
        Dim strClause As String = "[Sha512Hash]=@Hash AND [Size]=@Size"
        Dim sqlPar1 As New SqlCeParameter("@Hash", sha512Hash)
        Dim sqlPar2 As New SqlCeParameter("@Size", size)
        Return ObjectPersister.GetOne(Of Entities.SharedItemFile)(strClause, sqlPar1, sqlPar2)
    End Function

    Public Shared Function GetAllByShareId(ByVal shareId As Integer) As Generic.List(Of Entities.SharedItemFile)
        Dim strClause As String = String.Format("SharedItemId IN (SELECT Id FROM SharedItems WHERE ShareId = {0})", shareId)
        Return ObjectPersister.GetAll(Of Entities.SharedItemFile)(strClause)
    End Function

    Public Shared Sub SetFileAsOrphan(ByVal id As Integer, ByVal transactionScope As TransactionScope)
        Dim strSql As String
        strSql = String.Format("UPDATE SharedItemsFiles SET SharedItemId = 0, OrphanedSince=getdate(), Directory='' WHERE Id={0}", id)
        Environment.ExecuteNonQuery(strSql, transactionScope)
    End Sub
    Public Shared Sub Delete(ByVal id As Integer, ByVal transactionScope As TransactionScope)
        Dim strSql As String = String.Format("DELETE FROM SharedItemsFiles WHERE Id={0}", id)
        Environment.ExecuteNonQuery(strSql, transactionScope)
    End Sub
    Public Shared Sub SetFilesAsOrphansByShareId(ByVal shareId As Integer, ByVal transactionScope As TransactionScope)
        Dim strSql As String
        strSql = "UPDATE SharedItemsFiles SET SharedItemId = 0, OrphanedSince=getdate(), Directory='' WHERE SharedItemId IN (SELECT Id FROM SharedItems WHERE ShareId=@ShareId)"
        Dim sqlPar1 As New SqlCeParameter("@ShareId", shareId)
        Environment.ExecuteNonQuery(strSql, transactionScope, sqlPar1)
    End Sub
    Public Shared Sub SetFilesAsOrphansBySharedItemId(ByVal sharedItemId As Integer, ByVal transactionScope As TransactionScope)
        Dim strSql As String
        strSql = "UPDATE SharedItemsFiles SET SharedItemId = 0, OrphanedSince=getdate(), Directory='' WHERE SharedItemId = @SharedItemid"
        Dim sqlPar1 As New SqlCeParameter("@SharedItemId", sharedItemId)
        Environment.ExecuteNonQuery(strSql, transactionScope, sqlPar1)
    End Sub

    Public Shared Sub ReevalOrphanedSince(ByVal transactionScope As TransactionScope)
        Dim strSql As String = "UPDATE SharedItemsFiles SET OrphanedSince=getdate() " & _
                               "WHERE SharedItemId NOT IN (SELECT Id FROM SharedItems) " & _
                               "  AND OrphanedSince IS NULL"
        Environment.ExecuteNonQuery(strSql, transactionScope)

        strSql = "UPDATE SharedItemsFiles SET OrphanedSince=NULL " & _
                 "WHERE SharedItemId IN (SELECT Id FROM SharedItems) " & _
                 "  AND OrphanedSince IS NOT NULL"
        Environment.ExecuteNonQuery(strSql, transactionScope)
    End Sub

    Public Shared Sub RemoveOldOrphans(ByVal orphanedSince As Date, ByVal transactionScope As TransactionScope)
        Dim strSql As String = "DELETE FROM SharedItemsFiles WHERE OrphanedSince<@OrphanedSince AND OrphanedSince IS NOT NULL"
        Dim sqlCmd As Command = Environment.NewCommand(strSql, transactionScope)
        sqlCmd.AddParameterWithValue("@OrphanedSince", orphanedSince)

        Environment.ExecuteNonQuery(sqlCmd)
    End Sub

    Public Shared Sub Save(ByVal file As Entities.SharedItemFile)
        Using t As New TransactionScope(TransactionScope.TransactionMode.NullTransaction)
            Save(file, t)
            t.Complete()
        End Using
    End Sub

    Public Shared Sub Save(ByVal item As Entities.SharedItemFile, ByVal transactionScope As TransactionScope)
        Dim clsCmd As New SaveCommand(Of Integer)("SharedItemsFiles", "Id", item.Id, 0)
        clsCmd.AddParam("FileName", item.FileName, True)
        clsCmd.AddParam("Directory", item.Directory, True)
        clsCmd.AddParam("SHA512Hash", item.SHA512Hash, True)
        clsCmd.AddParam("Size", item.Size, True)
        clsCmd.AddParam("SharedItemId", item.SharedItemId, True)
        clsCmd.AddParam("Added", item.Added, True)
        clsCmd.AddParam("LastChanged", item.LastChanged, True)
        clsCmd.AddParam("SHA512HashForFirstMb", item.SHA512HashForFirstMb, True)
        clsCmd.AddParam("SHA512HashForLastMb", item.SHA512HashForLastMb, True)

        item.Id = clsCmd.Save(transactionScope)
    End Sub

End Class
